<?php

/** Esta clase es la encargada del manejo de los datos de la tabla services  **/

class Services
{
	/**
	 *	@access  public
	 *	@author <Juan Carlos Morales>
	 */
			
	public function __construct ()
	{
	
	
	}
	
	/**
	 * Esta funcion es la encargada de agregar los servicios que existiran en el sitio.		    
	 * @access public
	 */		
	
	public function addServices($data){
		$sql="INSERT INTO `services` (`ser_name`, `ser_info_large`, `ser_info_short`,`ser_name_e`, `ser_info_large_e`, `ser_info_short_e`,";
		$sql.=" `ser_req_country`, `ser_req_operator`, `ser_req_models`, `ser_req_MEP`,`ser_req_id`, `ser_req_user`, `ser_req_serie`, `ser_req_idbox`,";
		$sql.=" `ser_type_code`,`ser_status`,`ser_price`,`ser_time_response`,`ser_comment_models`,`ser_price_us`,`ser_price_bol`,`file_name`,`file_rename`,`img_name`,`img_rename`,`req_all_brand`,ser_req_nick,ser_req_pass,ser_req_imei,ser_req_rm, ser_price_purchase, ser_price_col_reseller, ser_price_us_reseller, ser_price_bol_reseller,ser_algoritmo,ser_form_liberation) VALUES (";
		$sql.="'$data->name','$data->info_large','$data->info_short','$data->name_e','$data->info_large_e','$data->info_short_e',$data->req_country,";
		$sql.="$data->req_operator,$data->req_models,$data->req_MEP,$data->req_id,$data->req_user,$data->req_serie,$data->req_idbox,$data->ser_type_code,$data->ser_status,'$data->priceCOL','$data->timeResponse','$data->commentModels','$data->priceUS','$data->priceBOL','$data->file_name','$data->file_rename','$data->img_name','$data->img_rename','$data->reqAllBrands',$data->ser_req_nick,$data->ser_req_pass,$data->ser_req_imei,$data->ser_req_rm, '$data->ser_price_purchase', '$data->priceCOLReseller', '$data->priceUSReseller', '$data->priceBOLReseller', '$data->algoritmo', '$data->form_liberation')";

		if(mysql_query($sql))
			return mysql_insert_id();
		else{
			securityMasterUnlock(64,1,$sql);
			return false;
		}	
 	}
	
	//permite actualizar un servicio
	function updateService($data){
		$sql = "
		UPDATE services SET 
		ser_name = '".$data->name."',
		ser_info_large = '".$data->info_large."',
		ser_info_short = '".$data->info_short."',
		ser_name_e = '".$data->name_e."',
		ser_info_large_e = '".$data->info_large_e."',
		ser_info_short_e = '".$data->info_short_e."',
		ser_req_country = '".$data->req_country."',
		ser_req_operator = '".$data->req_operator."',
		ser_req_models = '".$data->req_models."',
		ser_req_MEP = '".$data->req_MEP."',
		ser_req_id = '".$data->req_id."',
		ser_req_user = '".$data->req_user."',
		ser_req_serie = '".$data->req_serie."',
		ser_req_idbox = '".$data->req_idbox."',
		ser_type_code = '".$data->ser_type_code."',
		ser_status = '".$data->ser_status."',
		ser_price = '".$data->priceCOL."',
		ser_time_response = '".$data->timeResponse."',
		ser_price_us = '".$data->priceUS."',
		ser_price_bol = '".$data->priceBOL."',
		ser_price_col_reseller = '".$data->priceCOLReseller."',
		ser_price_us_reseller = '".$data->priceUSReseller."',
		ser_price_bol_reseller = '".$data->priceBOLReseller."',
		file_name = '".$data->file_name."',
		file_rename = '".$data->file_rename."',
		img_name = '".$data->img_name."',
		img_rename = '".$data->img_rename."' ,
		ser_req_nick = '".$data->ser_req_nick."' ,
		ser_req_pass = '".$data->ser_req_pass."' ,
		ser_req_imei = '".$data->ser_req_imei."' ,
		ser_req_rm = '".$data->ser_req_rm."' ,
		req_all_brand = '".$data->reqAllBrands."',
		ser_algoritmo = '".$data->algoritmo."',
		ser_form_liberation = '".$data->form_liberation."',
		ser_price_purchase = '".$data->ser_price_purchase."'
		WHERE ser_code=".(int)$data->code_service.";";
		if(mysql_query($sql))
		   return true;
		else{
			securityMasterUnlock(64,1,$sql);
			return false;
		}
	}
 	
/**
	 * Esta funcion es la encargada de agregar los servicios que existiran en el sitio.		    
	 * @access public
	 */		
	
	public function addServicesSync($data,$code=-1){
       if($code==-1){
       	 	$sql="INSERT INTO `services` (`ser_code_api`,`ser_name`, `ser_info_large`, `ser_info_short`,";
			$sql.=" `ser_req_country`, `ser_req_operator`, `ser_req_models`, `ser_req_MEP`,";
			$sql.=" `ser_type_code`,`ser_use_api`,`ser_status`,`api_code`,`ser_req_pin`,`ser_req_kbh`,`ser_req_prd`,`ser_req_type`) VALUES (";
			$sql.="'$data->ser_code_api','$data->name','$data->info_large','$data->info_short',$data->req_country,";
			$sql.="$data->req_operator,$data->req_models,$data->req_MEP,$data->ser_type_code,$data->req_api,$data->ser_status,$data->api_code,";
			$sql.="$data->req_pin,$data->req_kbh,$data->req_prd,$data->req_type)";
			if(mysql_query($sql))
			   return true;
			else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}   
							
       }else{
       	
       	 return $this->setServicesSync($data);
       	 
       }
 	}
 	
 	
	 /**
	 * Esta funcion es la encargada de listar los servicios que existiran en el sitio.		    
	 * @access public
	 * @return array con los datos encontrados en la consulta
	 *//////////////////////////////////////////////////////////////////////////////////	
	public function listServices($inicio = 0,$cantidad = 0,$buscar='',$serv_type='All',$order_by='', $ser_type_code='', $ser_status = -1 ){
		
		$where= '';
		if($buscar != '')
			$where= " WHERE ser_name like '%".$buscar."%'";
		
		if($serv_type!='All'){			
			if($where != '')
				$where.=" AND type_service ='".$serv_type."'";
			else
				$where.=" WHERE type_service ='".$serv_type."'";
		}
		
		if($ser_type_code != ''){
			if($where != '')
				$where.=" AND ser_type_code = '".$ser_type_code."'";
			else
				$where.=" WHERE ser_type_code = '".$ser_type_code."'";
		}
		
		if ( $ser_status != -1 ){
			if ( $where != '' )
				$where .= " AND ser_status = '".$ser_status."' ";
			else
				$where .= " WHERE ser_status = '".$ser_status."' ";
		}
		
		if(trim($order_by)=='')
			$order_by = " ORDER BY ser_order, ser_status DESC, TRIM(ser_name) ";
		
		$sql = "
		SELECT t.ser_code, t.ser_name, t.ser_info_large, t.ser_info_short, t.ser_name_e, t.ser_info_large_e, t.ser_info_short_e, t.ser_type_code, t.ser_price, t.ser_price_us,
		t.ser_price_bol, t.ser_status, t.ser_time_response, t.img_name, t.img_rename, t.ser_order, t.ser_price_purchase, t.API_provider_code, t.api_name, t.ser_type_name,
		t.type_service, t1.amount_liberations
		FROM
		(SELECT t1.ser_code, t1.ser_name, t1.ser_info_large, t1.ser_info_short, t1.ser_name_e, t1.ser_info_large_e, t1.ser_info_short_e, t1.ser_type_code, t1.ser_price,
		t1.ser_price_us, t1.ser_price_bol, t1.ser_status, t1.ser_time_response, t1.img_name, t1.img_rename, t1.ser_order, t1.ser_price_purchase, t1.API_provider_code,
		t1.api_name, st.ser_type_name, st.type_service
		FROM
		(SELECT t0.ser_code, t0.ser_name, t0.ser_info_large, t0.ser_info_short, t0.ser_name_e, t0.ser_info_large_e, t0.ser_info_short_e, t0.ser_type_code, t0.ser_price,
		t0.ser_price_us, t0.ser_price_bol, t0.ser_status, t0.ser_time_response, t0.img_name, t0.img_rename, t0.ser_order, t0.ser_price_purchase, t0.API_provider_code,
		api.api_name
		FROM
		(SELECT s.ser_code, s.ser_name, s.ser_info_large, s.ser_info_short, s.ser_name_e, s.ser_info_large_e, s.ser_info_short_e, s.ser_type_code, s.ser_price, s.ser_price_us,
		s.ser_price_bol, s.ser_status, s.ser_time_response, s.img_name, s.img_rename, s.ser_order, s.ser_price_purchase, at.API_provider_code
		FROM services s LEFT JOIN api_tool at ON s.ser_code = at.MU_tool_code) t0
		LEFT JOIN api ON t0.API_provider_code = api.api_code) t1		
		JOIN service_type st
		ON t1.ser_type_code = st.ser_type_code) t
		LEFT JOIN
		(SELECT COUNT(*) as amount_liberations, s.ser_code FROM services s JOIN liberations l ON s.ser_code = l.ser_code GROUP BY s.ser_code) t1
		ON t.ser_code = t1.ser_code	 ".$where."  ".$order_by." ";
		//echo $sql;
		if(!($inicio==0 && $cantidad==0))
			$sql .= " LIMIT ".$inicio.",".$cantidad.";";
		
		if($consulta = mysql_query($sql)){			
			$data = array();	
			$i=0;
			while($row=mysql_fetch_array($consulta)){
				$data[$i]->code = $row['ser_code'];
				$data[$i]->name = $row['ser_name'];			
				$data[$i]->info_large = $row['ser_info_large'];//substr($row['ser_info_large'],0,30).'...';
				$data[$i]->info_short = $row['ser_info_short'];
				
				$data[$i]->name_e = $row['ser_name_e'];
				$data[$i]->info_large_e = $row['ser_info_large_e'];
				$data[$i]->info_short_e = $row['ser_info_short_e'];
				
				$data[$i]->ser_type_code = $row['ser_type_code'];
				
				
				$data[$i]->ser_price = $row['ser_price'];
				$data[$i]->ser_price_us = $row['ser_price_us'];
				$data[$i]->ser_price_bol = $row['ser_price_bol'];
								
				$data[$i]->status = $row['ser_status'];
				$data[$i]->time_response = $row['ser_time_response'];
				
				$data[$i]->img_name = $row['img_name'];
				$data[$i]->img_rename = $row['img_rename'];
				
				$data[$i]->ser_order = $row['ser_order'];
				$data[$i]->ser_price_purchase = $row['ser_price_purchase'];
				
				$data[$i]->ser_type_name = $row['ser_type_name'];
				$data[$i]->type_service = $row['type_service'];
				
				$data[$i]->api_name = $row['api_name'];
				
				$data[$i]->amount_liberations = $row['amount_liberations'];
				
				$i++;
			}
				
			return $data;
		}
		else{
			securityMasterUnlock(64,1,$sql);
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////
	// funcion encargada de contar cuantas liberaciones se ha hecho por servicio.
	///////////////////////////////////////////////////////////
	function countLiberationsServices($ser_code)
	{
		$sql = "
		SELECT count(*) as cant
		FROM services ser 
		JOIN liberations lib 
		ON ser.ser_code = lib.ser_code AND ser.ser_code=".$ser_code;
		
		if($consult = mysql_query($sql)){
	
			$data = array();
			$row=mysql_fetch_array($consult);
			
			return $row['cant'];
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
	///////////////////////////////////////////////////////////
	// countServices : funcion encargada de contar los servicios que hay en el sitio.
	// @date         : 21.SEP.2010
	///////////////////////////////////////////////////////////
	public function countServices($buscar='',$serv_type='', $ser_type_code='', $ser_status = -1 ){
		$where= '';
		if($buscar != '')
			$where= " WHERE ser_name like '%".$buscar."%'";
		
		if($serv_type!='All'){			
			if($where != '')
				$where.=" AND type_service ='".$serv_type."'";
			else
				$where.=" WHERE type_service ='".$serv_type."'";
		}
		
		if($ser_type_code != ''){
			if($where != '')
				$where.=" AND ser_type_code = '".$ser_type_code."'";
			else
				$where.=" WHERE ser_type_code = '".$ser_type_code."'";
		}
		
		if ( $ser_status != -1 ){
			if ( $where != '' )
				$where .= " AND ser_status = '".$ser_status."' ";
			else
				$where .= " WHERE ser_status = '".$ser_status."' ";
		}
		
		$sql = "SELECT count(*) AS cant FROM services serv JOIN service_type ser_typ ON serv.ser_type_code = ser_typ.ser_type_code ".$where." ";
//		die($sql);
		if($consulta = mysql_query($sql)){
			if ( $row = mysql_fetch_array($consulta) )
				return $row['cant'];
			else
				return 0;
		}
		else {			
			securityMasterUnlock(64,1,$sql);
			return false;
		}
	}
	
	/**
	 * Esta funcion es la encargada de borrar los servicios de la base de datos.		    
	 * @access public
	 * @return array con los datos encontrados en la consulta
	 */		
	
	public function deleteServices ($code)
	{
		$sql = "DELETE FROM `services` WHERE `ser_code` = $code";
	 	if(mysql_query($sql))
		  return true;
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}  
	}
	
    /**
	 * Esta funcion se encarga de obtener la información de la api a la que esta asociada un servicio		    
	 * @access public
	 * @return el nombre de la api asociada al service type
	 */	
	 
	public function getApiNameByServiceCode ($code)
	{
		$sql = "SELECT a.api_name FROM services s, api a WHERE s.ser_code = ".$code." and s.api_code = a.api_code";
		//echo "Consulta: ".$sql;
		if($consulta = mysql_query($sql)){
		
			if($row=mysql_fetch_array($consulta)){
				return $row['api_name'];	
			}
			else{
				return "Ninguna";
			}
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
			
	}
	
	/**
	 * Esta funcion se encarga de obtener la lista de operadores desde la api unlockbase		    
	 * @access public
	 * @return la lista de operadores desde la api unlockbase
	 */	
	public function getNetworksByCountryApi($serviceCode,$countryCode,$apiName)
	{
		if($apiName=="Unlockbase"){				
			$serviceLocal = $this->getServices($serviceCode);
				
			if($serviceLocal->req_models){
					$XML = UnlockBase::CallAPI('GetNetworks');	
			 if (is_string($XML)){
			    $Data = UnlockBase::ParseXML($XML);
				
			    if (is_array($Data)){
				  if (isset($Data['Error'])){
						$networks = array();
				  }else{
				  	  
					  foreach ($Data['Country'] as $Country){
		                   if($Country['ID']==$countryCode){
		                   	 $i=0;
		                   	 foreach($Country['Network'] as $netWork){
		                   	    //echo "Nombre red: ".htmlspecialchars($netWork['Name']);
							 	$networks[$i]->code=$netWork['ID'];
							 	$networks[$i]->name=htmlspecialchars($netWork['Name']);
							 	$i++;
		                   	 }	
		                   }
					  	     
				       } 
				  }
			 	 }else{
				  	$networks = array();
				 } 
				 
			 }else{
			 	$networks =  array();
			 }
			}else{
				$networks =  array();
			}	 
		 	  return $networks;
		}else{
			//Aquí iría la invocación a otra api
			return array();
			
		}		
		
	}
	
/**
	 * Esta funcion se encarga de obtener los modelos de teléfonos a partir de la api utilizada		    
	 * @access public
	 * @return la lista de modelos asociados al servicio de la api y del país seleccionado
	 */	
	public function getModelsByApi($serviceCode,$apiName)
	{		
		if($apiName=="Unlockbase"){			
			$serviceLocal = $this->getServices($serviceCode);
				
			if($serviceLocal->req_models){
					$XML = UnlockBase::CallAPI('GetToolMobiles', array('ID' => $serviceCode));	
			 if (is_string($XML)){
			    $Data = UnlockBase::ParseXML($XML);
				
			    if (is_array($Data)){
				  if (isset($Data['Error'])){
						$models = array();
				  }else{
				  	  
				  	  $i=0;
					  foreach ($Data['Mobile'] as $mobile){
		                	 	$models[$i]->code=$mobile['ID'];
							 	$models[$i]->name=htmlspecialchars($mobile['Name']);
							 	$i++;
		              }	
		           }
			 	 }else{
				  	$models = array();
				 } 
				 
			 }else{
			 	$models =  array();
			 }
			}else{
				$models =  array();
			}	 
		 	  return $models;
				
		}else{
			//Aquí iría la otra api
			return array();
			
		}
	}
	
	
	/**
	 * Esta funcion es la encargada de obtener los datos de los servicios de la base de datos.		    
	 * @access public
	 * @return array con los datos encontrados en la consulta, si no encuentra nada retorna vacío
	 */	
	 
	function getServices($code)
	{ 
		$sql = "SELECT s.*,st.* FROM services s, service_type st WHERE s.ser_code = ". (int) $code." and s.ser_type_code = st.ser_type_code";		
		
		if($consulta = mysql_query($sql)){
		
			$row=mysql_fetch_array($consulta);
			
			$data->ser_code 	 = $row['ser_code'];
			$data->ser_type_code = $row['ser_type_code'];
			$data->ser_type_name = $row['ser_type_name'];
			$data->name          = $row['ser_name'];
			$data->info_large    = $row['ser_info_large'];
			$data->info_short    = $row['ser_info_short'];
			
			$data->name_e          = $row['ser_name_e'];
			$data->info_large_e    = $row['ser_info_large_e'];
			$data->info_short_e    = $row['ser_info_short_e'];
			
			$data->req_country   = $row['ser_req_country'];
			$data->req_operator  = $row['ser_req_operator'];
			$data->req_models    = $row['ser_req_models'];
			$data->req_MEP       = $row['ser_req_MEP'];
			$data->req_id   	 = $row['ser_req_id'];
			$data->req_serie  	 = $row['ser_req_serie'];
			$data->req_user    	 = $row['ser_req_user'];
			$data->req_idbox     = $row['ser_req_idbox'];
			$data->req_api       = $row['ser_use_api'];
			
			$data->ser_req_nick  = $row['ser_req_nick'];
			$data->ser_req_pass  = $row['ser_req_pass'];
			$data->ser_req_imei  = $row['ser_req_imei'];
			$data->ser_req_rm	 = $row['ser_req_rm'];
			//$data->req_pin       = $row['ser_req_pin'];
			//$data->req_kbh       = $row['ser_req_kbh'];
			//$data->req_prd       = $row['ser_req_prd'];
			//$data->req_type      = $row['ser_req_type'];
			$data->ser_status    = ($row['ser_status']      =='1')?'ACTIVO':'INACTIVO';
			$data->api_code      = $row['api_code'];
			$data->api_priceCOL  = $row['ser_price'];
			$data->api_priceUS   = $row['ser_price_us'];
			$data->api_priceBOL  = $row['ser_price_bol'];
			
			$data->api_priceCOL_reseller  = $row['ser_price_col_reseller'];
			$data->api_priceUS_reseller   = $row['ser_price_us_reseller'];
			$data->api_priceBOL_reseller  = $row['ser_price_bol_reseller'];
			
			$data->time_response = $row['ser_time_response'];
			
			$data->ser_img_name	 = $row['img_name'];
			$data->ser_img_rename= $row['img_rename'];
			
			$data->file_name	 = $row['file_name'];
			$data->file_rename	 = $row['file_rename'];
			
			$data->type_service  = $row['type_service']; 
			
			$data->req_all_brand  = $row['req_all_brand']; 
			
			$data->ser_price_purchase = $row['ser_price_purchase'];
			
			$data->ser_algoritmo = $row['ser_algoritmo'];
			$data->ser_form_liberation = $row['ser_form_liberation'];
			
			return $data;
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
	
	/**
	 * Esta funcion es la encargada de obtener la lista de países asociados a una api.		    
	 * @access public
	 * @return array con los datos encontrados en la consulta, si no encuentra nada retorna vacío
	 */	
	 
	public function getCountriesByApi ($code,$apiName){
	
	if($apiName=="Unlockbase"){	
			$serviceLocal = $this->getServices($code);
				
			if($serviceLocal->req_country){
					$XML = UnlockBase::CallAPI('GetNetworks');	
			 if (is_string($XML)){
			    $Data = UnlockBase::ParseXML($XML);
				
			    if (is_array($Data)){
				  if (isset($Data['Error'])){
						$pais = array();
				  }else{
				  	  $i=0;
					  foreach ($Data['Country'] as $Country){ 
							 $pais[$i]->code=$Country['ID'];
							 $pais[$i]->name=$Country['Name'];
							 $i++;
				       } 
				  }
			 	 }else{
				  	$pais = array();
				 } 
				 
			 }else{
			 	$pais =  array();
			 }
			}else{
				$pais =  array();
			}	 
 	  return $pais;
	}else{
		//Acá iría la invocación a la otra api
		return array();
	} 
}
	
	
/**
	 * Esta funcion es la encargada de modificar los datos de la tabla services de la base de datos al momento de realizar la sincronización con la api		    
	 * @access public
	 * @return true si se logró realizar la actualización, false si no se pudo realizar la inserción
	 */	
	
	public function setServicesSync ($new_data)
	{
		$sql =
		"UPDATE `services` SET 
		`ser_info_large`	= '".$new_data->info_large."',
		`ser_info_short`	= '".$new_data->info_short."',
		`ser_req_country`	= '".$new_data->req_country."',
		`ser_req_operator` 	= '".$new_data->req_operator."',
		`ser_req_models`	= '".$new_data->req_models."',
		`ser_req_MEP` 		= '".$new_data->req_MEP."',
		`ser_use_api` 		= '".$new_data->req_api."',
		`api_code` 		    = '".$new_data->api_code."',
		`ser_status` 		= '".$new_data->ser_status."'";
		
		$sql.=", `ser_type_code` 	= '".$new_data->ser_type_code."'";
		$sql.=", `ser_name`		 	= '".$new_data->name."'";
		
		   
		$sql.=" WHERE `ser_code_api` =".$new_data->ser_code_api;
		//echo "Código: ".$new_data->ser_code_api."<br>";
		//echo " Consulta = ".$sql;
		if(mysql_query($sql))
		   return true;
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}   
	}
	
	
	/**
	 * Esta funcion es la encargada de modificar los datos de la tabla services de la base de datos desde la parte de administración.		    
	 * @access public
	 * @return @return true si se logró realizar la inserción, false si no se pudo realizar la inserción
	 */	
	
			
	public function setServices ($new_data)
	{
		$sql =
		"UPDATE `services` SET 
		`ser_name`			= '".$new_data->name."',
		`ser_info_large`	= '".$new_data->info_large."',
		`ser_info_short`	= '".$new_data->info_short."',
		`ser_name_e`		= '".$new_data->name_e."',
		`ser_info_large_e`	= '".$new_data->info_large_e."',
		`ser_info_short_e`	= '".$new_data->info_short_e."',
		`ser_req_country`	= '".$new_data->req_country."',
		`ser_req_operator` 	= '".$new_data->req_operator."',
		`ser_req_models`	= '".$new_data->req_models."',
		`ser_req_MEP` 		= '".$new_data->req_MEP."',
		`ser_req_id`		= '".$new_data->req_id."',
		`ser_req_serie` 	= '".$new_data->req_serie."',
		`ser_req_user`		= '".$new_data->req_user."',
		`ser_req_idbox`		= '".$new_data->req_idbox."',
		`ser_type_code` 	= '".$new_data->ser_type_code."',
		`ser_use_api` 		= '".$new_data->req_api."',
		`api_code` 		    = '".$new_data->api_code."',
		`ser_status` 		= '".$new_data->ser_status."',
		`ser_price` 		= '".$new_data->priceCOL."',
		`ser_time_response` = '".$new_data->timeResponse."',
		`ser_comment_models`= '".$new_data->commentModels."',
		`ser_price_us` 		= '".$new_data->priceUS."',
		`ser_price_bol` 	= '".$new_data->priceBOL."'";		
		   
		$sql.="WHERE `ser_code` =".$new_data->ser_code;
		//echo " Consulta = ".$sql;
		if(mysql_query($sql))
		   return true;
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
	/*
	 * Esta funcion es la encargada eliminar un documento de un servicio obteniendo como parametro el nombre del documento a eliminar
	 * @access public
	 * @param $lido_codigo --> Codigo del documento a eliminar
	 */	
	public function deleteFile ($code)
	{
		$sql = 
		"UPDATE `services` SET 
		`ser_file_name`		= '',
		`ser_file_rename`	= ''	
		WHERE ser_code = ".$code;  
		
	 	if(mysql_query($sql))
		   return true;
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
	/**
	*	Funcion encargada de listar los servicios activos.
	*	@autor: JSL
	*	@date: 01/11/2012
	*/	
	public function listAllServices($type_service = ''){
		$where = '';
		if( $type_service != '' )
			$where = " WHERE st.type_service = '".$type_service."' ";
		
		$sql = "SELECT s.ser_code, s.ser_name, s.ser_price_col_reseller, s.ser_price_us_reseller, s.ser_price_bol_reseller, s.ser_price, s.ser_price_us, s.ser_price_bol FROM service_type st JOIN services s ON st.ser_type_code = s.ser_type_code ".$where." ORDER BY st.ser_type_order, s.ser_order;";
		
		if($consulta = mysql_query($sql)){
			$data = array();
			$i=0;
			while($row = mysql_fetch_array($consulta)){
				$data[$i]->code = $row['ser_code'];
				$data[$i]->name = $row['ser_name'];
				
				$data[$i]->ser_price_col_reseller = $row['ser_price_col_reseller'];
				$data[$i]->ser_price_us_reseller = $row['ser_price_us_reseller'];
				$data[$i]->ser_price_bol_reseller = $row['ser_price_bol_reseller'];
				
				$data[$i]->ser_price_col = $row['ser_price'];
				$data[$i]->ser_price_us = $row['ser_price_us'];
				$data[$i]->ser_price_bol = $row['ser_price_bol'];
				
				$i++;
			}
			return $data;
		}
		else{
			securityMasterUnlock(64,1,$sql);
			return false;
		}
	}
	
	/**
	* Esta funcion se encarga de obtener todos los países existentes en el sistema .		    
	* @access public
	* @return array con los datos de todos los páises encontrados
	*/
	
	public function listAllCountries(){
		$where= '';
		$sql = "SELECT * FROM country ".$where." ORDER BY cou_name";
		if($consulta = mysql_query($sql)){
	
			$data = array();	
			$i=0;
			while($row=mysql_fetch_array($consulta)){
				$data[$i]->code			= $row['cou_code'];
				$data[$i]->name			= $row['cou_name'];
				$i++;
			}
			return $data;
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
/**
	 * Esta funcion se encarga de obtener todos los modelos asociados a un país .		    
	 * @access public
	 * @return array con los datos de todos los modelos asociados a un país
	 *//////////////////////////////////////////////////////////////////////////////////			
	
	public function getModelsByCountry($code)
	{
		$where= 'where mc.cou_code='.$code.' and (mc.cou_code=c.cou_code and mc.mod_code=m.mod_code)';
		
			$sql = "SELECT * FROM model_country mc, model m, country c ".$where." ORDER BY m.mod_name";
		    if($consulta = mysql_query($sql)) {
		
				$data = array();	
				$i=0;
				while($row=mysql_fetch_array($consulta))
				{
					$data[$i]->code			= $row['mod_code'];
					$data[$i]->name			= $row['mod_name'];
					$i++;
				}
				
		return $data;
			}
			else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
		
	}
	
/**
	 * Esta funcion se encarga de saber si un servicio existe o no por su código
	 * @access public
	 * @return false si no existe ningún registro, true si existe un registro
	 *//////////////////////////////////////////////////////////////////////////////////			
	
	public function existServiceByCodeApi($code, $apiCode,$serviceType){
		$where= 'WHERE ser_type_code_api='.$serviceType;
	
		$sql = "SELECT ser_type_code FROM service_type ".$where;
		if($consulta = mysql_query($sql)){
		
			$data = array();	
			
			if($row=mysql_fetch_array($consulta))
				$codigoServiceType=$row['ser_type_code'];
			else
				$codigoServiceType=0;
			
			$where= 'WHERE ser_code_api='.$code." and ser_type_code=".$codigoServiceType." and api_code=".$apiCode;
		
			$sql = "SELECT * FROM services ".$where;
			if($consulta = mysql_query($sql)){			
				$data = array();	
				$i=0;
				while($row=mysql_fetch_array($consulta))
				{
					$i++;
				}
				if($i==0){
					return false;
				}else{
					return true; 
				}
			}
			else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
 /**
	 * Esta funcion se encarga de obtener el código del  servicio asignado por la api por el código del  de servicio asigando por la bd		    
	 * @access public
	 * @return Código del servicio asignado por api
	 *//////////////////////////////////////////////////////////////////////////////////			
	
	public function getServiceCodeApiByServiceCodeBD($ser_code)
	{
	
		$where= " WHERE ser_code =".$ser_code;
		
			$sql = "SELECT ser_code_api FROM services ".$where;
		    if($consulta = mysql_query($sql)){
		
					if($row=mysql_fetch_array($consulta))
					{
						$serviceCodeApi			= $row['ser_code_api'];
					
					}
					else{
						$serviceCodeApi=0;
					}
					
				return $serviceCodeApi;
			}
			else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	
	}
	
	 /**
	 * Esta funcion se encarga de listar los modelos, por ahora todos los modelos, despues serian los modelos por marcas, depende.    
	 * @access public
	 * @return Código del servicio asignado por api
	 *//////////////////////////////////////////////////////////////////////////////////	
	public function listModels()
	{	
			$sql = "SELECT mod_code, mod_name FROM model ";
		    if($consulta = mysql_query($sql)) {
					$datos = array();
					$i=0;
					while($row=mysql_fetch_array($consulta))
					{
						$datos[$i]->code	= $row['mod_code'];
						$datos[$i]->name	= $row['mod_name'];
						$i++;
					}
					
				return $datos;
			}
			else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
	/**
	 * Esta funcion se encarga de listar los modelos, por ahora todos los modelos, despues serian los modelos por marcas, depende.    
	 * @access public
	 * @return Código del servicio asignado por api
	 *//////////////////////////////////////////////////////////////////////////////////	
	public function listBrands(){
		$sql = "SELECT b.bra_code, b.bra_name FROM brand b ORDER BY b.bra_name;";
		if($consulta = mysql_query($sql)){
			$datos = array();
			$i=0;
			while($row=mysql_fetch_array($consulta)){
				$datos[$i]->code = $row['bra_code'];
				$datos[$i]->name = $row['bra_name'];
				$i++;
			}
			
			return $datos;
		}
		else{
			securityMasterUnlock(64,1,$sql);
			return false;
		}
	}
	
	
	/**
	 * Esta funcion se encarga de listar los operadores por pais.
	 * @access public
	 * @return Código del servicio asignado por api
	 *//////////////////////////////////////////////////////////////////////////////////	
	public function listNetworks($codCountry)
	{	
			$sql = "SELECT red_id, red_name FROM red WHERE cou_code = ".$codCountry;
		    if($consulta = mysql_query($sql)){
					$datos = array();
					$i=0;
					while($row=mysql_fetch_array($consulta))
					{
						$datos[$i]->code	= $row['red_id'];
						$datos[$i]->name	= $row['red_name'];
						$i++;
					}
					
				return $datos;
			}
			else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
	
	//////////////////////////////////////////////////////////////////////////////////////////////
	// addModelToService : funcion encargada de asociar un modelo a un servicio.
	//////////////////////////////////////////////////////////////////////////////////////////////
	function addModelToService($mod_code,$ser_code)
	{
		$sql = "
		INSERT INTO `services_model` (
		`semo_code` ,
		`mod_code` ,
		`ser_code`
		)
		VALUES (
		NULL , '".$mod_code."', '".$ser_code."'
		);";
		
		if(mysql_query($sql))
		   return true;
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}   
	}

	
	//////////////////////////////////////////////////////////////////////////////////////////////
	// deleteAllModelsBySerice : funcion encargada de eliminar todos los modelos asociados a un servicio.
	//////////////////////////////////////////////////////////////////////////////////////////////
	function deleteAllModelsByService($serv_code){
		$sql = "DELETE FROM services_model WHERE ser_code = ".$serv_code.";";
		return mysql_query($sql);
	}
	
	//////////////////////////////////////////////////////////////////////////////////////////////
	// deleteModelByService : funcion encargada de eliminar un modelo asociado a un servicio.
	//////////////////////////////////////////////////////////////////////////////////////////////
	function deleteModelByService($mod_code)
	{
		$sql = "DELETE FROM `services_model` WHERE `mod_code` =".$mod_code;
		if(mysql_query($sql))
		   return true;
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			} 
	}	
	
	
	//////////////////////////////////////////////////////////////////////////////////////////////
	// listModelByService : funcion encargada de listar los modelos asociados al un servicio
	//////////////////////////////////////////////////////////////////////////////////////////////
	function listModelByService($serv_code){
		$data = array();	
		$sql = "
		SELECT modl.mod_code, modl.mod_name
		FROM services_model AS se_mo, model AS modl, services AS serv
		WHERE se_mo.ser_code = serv.ser_code
		AND se_mo.mod_code = modl.mod_code
		AND serv.ser_code =".$serv_code;
		
		if($consult  = mysql_query($sql)){
			$i = 0;
			while($row = mysql_fetch_array($consult)){
				$data[$i] = $row['mod_code'];
				$i++;				
			}
			
			return $data;
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			} 
	}
	
	//////////////////////////////////////////////////////////////////////////////////////////////
	// listModelByService : funcion encargada de listar los modelos asociados al un servicio
	//////////////////////////////////////////////////////////////////////////////////////////////
	function listModelByServiceAndBrand($serv_code,$brand_code)
	{		
		$sql = "
		SELECT modl.mod_code
		FROM services_model AS se_mo, model AS modl, services AS serv
		WHERE se_mo.ser_code = serv.ser_code
		AND se_mo.mod_code = modl.mod_code
		AND modl.bra_code =".$brand_code."
		AND serv.ser_code =".$serv_code;
		
		if($consult  = mysql_query($sql)){
			$i = 0;
			while($row = mysql_fetch_array($consult))
			{			
				$data[$i]    		= $row['mod_code'];	
				
				$i++;				
			}
			
			return $data;
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			} 
	}
	
	 /**
	 * Esta funcion se encarga de listar los modelos, por ahora todos los modelos, despues serian los modelos por marcas, depende.    
	 * @access public
	 * @return Código del servicio asignado por api
	 *//////////////////////////////////////////////////////////////////////////////////	
	public function listModelsPublic($codServices)
	{	
			$sql = "SELECT mode.mod_code, mod_name FROM model AS mode, services_model AS semo WHERE mode.mod_code = semo.mod_code AND ser_code = ".$codServices;
		    if($consulta = mysql_query($sql)){
					$datos = array();
					$i=0;
					while($row=mysql_fetch_array($consulta))
					{
						$datos[$i]->code	= $row['mod_code'];
						$datos[$i]->name	= $row['mod_name'];
						$i++;
					}
					
				return $datos;
			}
			else{
				securityMasterUnlock(64,1,$sql);
				return false;
			} 
	}
	
	//obtiene todos los codigos de las marcas de un servicio
	function getBrandsByService($code_service)
	{
		$data = array();
		$sql = "
		SELECT brn.bra_code FROM `services_model` AS sermod, model AS modl , brand AS brn
		WHERE sermod.mod_code = modl.mod_code AND modl.bra_code=brn.bra_code AND sermod.`ser_code`=".$code_service."
		group by brn.bra_code";
		
		if($consult = mysql_query($sql)){
			$i=0;
			while($row = mysql_fetch_array($consult))
			{
				$data[$i]= $row['bra_code'];
				$i++;
			}
			
			return $data;
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			} 
	}
	
	///////////////////////////////////////////////////////////	
	//	FUNCION ENCARGADA DE AGREGAR UN NUEO ARCHIVO AL SERVICIO, PUEDE SER IMAGE O FILE.
	//	NOTE: flserv_type---> img ó file
	//	AUTHOR: JSEBASTIANLARA
	//	DATE: 04.DIC.2010 
	///////////////////////////////////////////////////////////
	function addFileService($data)
	{
		$sql = "
		INSERT INTO `service_file` (
		`flserv_code`,
		`service_code`,
		`flserv_name`,
		`flserv_rename`,
		`flserv_type`) VALUES (
		NULL,
		'".$data->service_code."',
		'".$data->flserv_name."',
		'".$data->flserv_rename."', 
		'".$data->flserv_type."');";

		if(mysql_query($sql))
		    return true;
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			} 	
	}
	
	///////////////////////////////////////////////////////////	
	//	FUNCION ENCARGADA DE LISTAR LOS ARCHIVOS E IMAGES DE UN SERVICIO
	//	NOTE: flserv_type---> img ó file
	//	AUTHOR: JSEBASTIANLARA
	//	DATE: 04.DIC.2010 
	///////////////////////////////////////////////////////////
	function getServiceFile($service_code)
	{
		$data = array();
		$sql = "
		SELECT flserv_code,service_code,flserv_name,flserv_rename,flserv_type 
		FROM service_file 
		WHERE service_code=".$service_code;
		
		if($consult = mysql_query($sql)){
			$i=0;
			while($row = mysql_fetch_array($consult))
			{
				$data[$i]->flserv_code	 	= $row['flserv_code'];
				$data[$i]->service_code 	= $row['service_code'];
				$data[$i]->flserv_name		= $row['flserv_name'];
				$data[$i]->flserv_rename	= $row['flserv_rename'];
				$data[$i]->flserv_type		= $row['flserv_type'];
				
				$i++;
			}
			
			return $data;
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			} 
	}
	
	///////////////////////////////////////////////////////////	
	//	FUNCION ENCARGADA DE OBTENER EL RENAME DE UNA IMAGE
	//	AUTHOR: JSEBASTIANLARA
	//	DATE: 04.DIC.2010 
	///////////////////////////////////////////////////////////
	function getRenameImageDoc($flserv_code)
	{
		$sql = "SELECT flserv_rename FROM service_file WHERE flserv_code=".$flserv_code;
		if($consult = mysql_query($sql)){
			$row =  mysql_fetch_array($consult);
	
			return $row['flserv_rename'];
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			} 
	}
	
	///////////////////////////////////////////////////////////	
	//	FUNCION ENCARGADA DE ELIMINAR UNA IMAGE DEL SERVICIO
	//	AUTHOR: JSEBASTIANLARA
	//	DATE: 04.DIC.2010 
	///////////////////////////////////////////////////////////
	function removeImageDocServices($flserv_code)
	{
		$sql = "DELETE FROM `service_file` WHERE `flserv_code` =".$flserv_code;
		if(mysql_query($sql))
		  return true;
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}   
	}
	
	//////////////////////////////////////////////////////////////////////////
	//	funcion encargada de modificar los precios de un servicio a un cliente
	//////////////////////////////////////////////////////////////////////////
	function setPriceService($data)
	{
		$sql = "UPDATE `client_price` SET `clpr_price_col` = '".$data->change_col."',
		`clpr_price_us` = '".$data->change_us."',
		`clpr_price_bol` = '".$data->change_bol."' WHERE `clpr_code` =".$data->clpr_code;		
		if(mysql_query($sql))
		  return true;
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}  
	}
	
	//////////////////////////////////////////////////////////////////////////
	// funcion encargada de obtener los precios que tienen cada servicio por cliente.
	//////////////////////////////////////////////////////////////////////////
	function getPricesServicesClient($ser_code){
		$sql = "SELECT cp.clpr_code, cp.clpr_price_col, cp.clpr_price_us, cp.clpr_price_bol FROM client_price cp WHERE cp.ser_code=".(int)$ser_code;
		if($consult = mysql_query($sql)){
			$data = array();
			$i=0;
			while($row = mysql_fetch_array($consult)){
				$data[$i]->clpr_code	  = $row["clpr_code"]; 
				$data[$i]->clpr_price_us  = $row["clpr_price_us"];
				$data[$i]->clpr_price_col = $row["clpr_price_col"];
				$data[$i]->clpr_price_bol = $row["clpr_price_bol"];
				$i++;
			}
			
			return $data;
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////////////
	// funcion encargada de obtenet el tipo de servicio(Imei,Cable,Archivo), y los precios para cada moneda.
	///////////////////////////////////////////////////////////////////////////////////////////////////////
	function getServicesDataSecure($ser_code,$cli_code){
		$sql = "SELECT cp.clpr_price_col, cp.clpr_price_us, cp.clpr_price_bol FROM client_price cp WHERE cp.ser_code=".(int) $ser_code.' AND cp.cli_code='.(int) $cli_code.";";		
		if($consult = mysql_query($sql)) {
			if($row = mysql_fetch_array($consult)){
				$data->ser_price	= $row['clpr_price_col'];//colombia
				$data->ser_price_us = $row['clpr_price_us'];
				$data->ser_price_bol= $row['clpr_price_bol'];
				return $data;
			}
			else return false;
		}
		else{
			securityMasterUnlock(64,1,$sql);
			return false;
		}
	}
	
	////////////////////////////////////////////////////////////////////////////
	// Funcion encargada de obtener nombre y codigo de las marcas de un servicio
	////////////////////////////////////////////////////////////////////////////
	function getServiceBrandCheck($code_service)
	{
		$data = array();
		$sql = "
		SELECT brn.bra_code,brn.bra_name FROM `services_model` AS sermod, model AS modl , brand AS brn
		WHERE sermod.mod_code = modl.mod_code AND modl.bra_code=brn.bra_code AND sermod.`ser_code`=".$code_service."
		group by brn.bra_code ORDER BY brn.bra_name;";
		
		if($consult = mysql_query($sql)){
			$i=0;
			while($row = mysql_fetch_array($consult))
			{
				$data[$i]->bra_code= $row['bra_code'];
				$data[$i]->bra_name= $row['bra_name'];
				$i++;
			}
			
			return $data;
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
	////////////////////////////////////////////////////////////////////////////
	// Funcion encargada de obtener nombre y codigo de los modelos de una marca
	////////////////////////////////////////////////////////////////////////////
	function getModelsByBrand($bra_code)
	{
		$data = array();
		$sql = "SELECT mod_code,mod_name FROM `model` WHERE `bra_code`=".$bra_code;
		
		if($consult = mysql_query($sql)){
			$i=0;
			while($row = mysql_fetch_array($consult))
			{
				$data[$i]->mod_code= $row['mod_code'];
				$data[$i]->mod_name= $row['mod_name'];
				$i++;
			}
			
			return $data;
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
	////////////////////////////////////////////////////////////////////////////
	// Funcion encargada de obtener nombre y codigo de los modelos de una marca
	////////////////////////////////////////////////////////////////////////////
	function getModelsByBrandSpecify($bra_code,$ser_code)
	{
		$data = array();
		$sql = "SELECT m.mod_code,mod_name FROM model AS m, services_model AS s WHERE s.ser_code = $ser_code AND m.bra_code=$bra_code AND m.mod_code = s.mod_code ";
		
		if($consult = mysql_query($sql)){
			$i=0;
			while($row = mysql_fetch_array($consult))
			{
				$data[$i]->mod_code= $row['mod_code'];
				$data[$i]->mod_name= $row['mod_name'];
				$i++;
			}
			
			return $data;
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
	/**
	 *	Esta función se encarga de obtener todos los servicios Master-unlock,
	 *	tambien obtendrá sólo los servicios de Mster-Unlock asociados a un grupo en una API en caso de que el cod. de la API llegue por parámetro.
	 *	@parameter: $service_type	-->	tipo de servicio del cual necesitamos los servicios.
	 *				$API_provider_code	--> código de la API.
	 *	@autor: jsl.
	 *	@date: 12 AGO 2011
	 */
	public function listServiceByServiceType($type_service='',$API_provider_code=0){
		$where = "";
		if($API_provider_code > 0)
			$where = " WHERE at.API_provider_code=".$API_provider_code." ";
		$sql = "
		SELECT at.API_tool_code, IFNULL(at.API_provider_code,0) AS API_provider_code, IFNULL(at.API_provider_tool_code,0) AS API_provider_tool_code,
		t0.ser_type_code, t0.ser_type_name,	t0.ser_type_name_e,	t0.ser_code, t0.ser_name, t0.ser_name_e, t0.ser_price, t0.ser_price_us, t0.ser_price_bol 
		FROM
		(SELECT st.ser_type_code, st.ser_type_name, st.ser_type_name_e, st.ser_type_order, s.ser_code, s.ser_name, s.ser_name_e, s.ser_price, s.ser_price_us, s.ser_price_bol,
		s.ser_order 
		FROM service_type st JOIN services s ON st.ser_type_code=s.ser_type_code WHERE st.type_service='".$type_service."' AND s.ser_status='1') t0
		LEFT JOIN api_tool at 
		ON at.MU_tool_code=t0.ser_code ".$where." ORDER BY t0.ser_type_order, t0.ser_order, t0.ser_name";
		$data = array();
		if($result = mysql_query($sql)){
			$i=0;
			while($row=mysql_fetch_array($result)){
				$data[$i]->API_tool_code = $row["API_tool_code"];
				$data[$i]->API_provider_code  = $row["API_provider_code"];
				$data[$i]->API_provider_tool_code = $row["API_provider_tool_code"];//código del servicio en la API.
				
				$data[$i]->ser_type_code = $row["ser_type_code"];
				$data[$i]->ser_type_name = $row["ser_type_name"];
				$data[$i]->ser_type_name_e = $row["ser_type_name_e"];
				$data[$i]->ser_code = $row["ser_code"];
				$data[$i]->ser_name = $row["ser_name"];
				$data[$i]->ser_name_e = $row["ser_name_e"];
				$data[$i]->ser_price = $row["ser_price"];
				$data[$i]->ser_price_us = $row["ser_price_us"];
				$data[$i]->ser_price_bol = $row["ser_price_bol"];
								
				$i++;
			}			
			return $data;
		}
		else{
			securityMasterUnlock(64,1,$sql);
			return false;
		}
	}
	
	/**
	*	Funcion encargada de editar informacion especifica de un sevicio
	*	@autor_ JSL
	*	@date: 23 NOV 2011
	*/
	function setDataService($data, $action = ''){
		$set = "";
		switch ( $action ) {
			case 1://tiempo de respuesta
				$set = " ser_time_response='".$data->ser_time_response."' ";
			break;
			
			case 2://precio de compra
				$set = " ser_price_purchase=".$data->ser_price_purchase.", ser_price=".$data->new_price_col." ,ser_price_us=".$data->new_price_us.",ser_price_bol=".$data->new_price_bol;
			break;
			
			case 3://estado
				$set = " ser_status='".$data->ser_status."' ";
			break;
			
			default:
				return false;
			break;			
		}
		
		$sql = "UPDATE services SET ".$set." WHERE ser_code=".(int) $data->ser_code.";";
		
		if(mysql_query($sql))
		   return true;
		else{
			securityMasterUnlock(64,1,$sql);
			return false;
		}   
	}
		
	/**
	*	Funcion encargada de listar los grupos de servicio
	*	@autor: JSL
	*	@date: 02/01/2012
	*/
	function listServiceType($type_service = ''){
		$where = '';
		if($type_service != '')
			$where = " WHERE st.type_service='".$type_service."'";
		
		$sql = "SELECT st.ser_type_code, st.ser_type_name FROM service_type st ".$where." ORDER BY st.ser_type_order;";
		if( $result = mysql_query($sql) ){
			$i = 0;
			while( $row = mysql_fetch_array($result) ){
				$data[$i]->ser_type_code = $row["ser_type_code"];
				$data[$i]->ser_type_name = $row["ser_type_name"];
				$i++;
			}
			return $data;
		}
		else{
			securityMasterUnlock(64,1,$sql);
			return NULL;
		}
	}
	
	/**
	*	Funcion encargada de retornar un arreglo con los numeros de orden usados por los servicios.
	*	@autor: JSL
	*	@date: 02/01/2012
	*/
	function getArrayOrdersServices($ser_type_code = 0){
		$sql = "SELECT s.ser_order FROM services s WHERE s.ser_type_code = ".$ser_type_code." GROUP BY s.ser_order;";
		if($result = mysql_query($sql)){
			$i = 0;
			while($row = mysql_fetch_array($result)){
				$data[$i] = $row["ser_order"];
				$i++;
			}
			return $data;
		}
		else{
			securityMasterUnlock(64,1,$sql);
			return NULL;
		}
	}
	
	/**
	*	Funcion encargada de modificar el orden para servicio.
	*	@autor: JSL
	*	@date: 02/01/2011
	*/
	function updateOrderServices($data){
		$sql = "UPDATE services SET ser_order = '".$data->ser_order."' WHERE ser_code=".$data->ser_code;
		return mysql_query($sql);
	}
	
	/**
	*	Funcion encargada de listar los servicios que han sido utilizados por los clientes y la cantidad de creditos gastados en los mismos
	*	@author: JSL
	*	@date: 10/MAR/2012
	*/
	function listServicesUtility($order_by = '', $lib_date_from = '', $lib_date_to = ''){
		$where = '';
		
		if ( $lib_date_from != '')
			$where = " AND DATE(libimei_date_liberation) >= '".$lib_date_from."' ";
		
		if ( $lib_date_to != '')
			$where .= " AND DATE(libimei_date_liberation) <= '".$lib_date_to."' ";
		
		$sql = "
		SELECT s.ser_name, t1.ser_code, t1.libimei_money, t1.cant, t1.bonus, t1.price_purchase, s.ser_price_purchase FROM
		(
		SELECT t0.cant, t1.ser_code, t1.libimei_money, t1.bonus, t1.price_purchase FROM
		(SELECT t1.ser_code, t1.libimei_money, COUNT(t1.libimei_bonus) AS cant FROM
		
		(SELECT l.lib_code, l.ser_code, l.lib_date, li.libimei_code, li.libimei_bonus, li.libimei_money FROM 
		(SELECT l.lib_code, l.ser_code, l.lib_date FROM liberations l JOIN client c ON l.cli_code=c.cli_code WHERE c.cli_type <> 3 ) l 
		JOIN operator_notice li
		ON l.lib_code=li.lib_code WHERE li.libimei_status=1 ".$where.") t1 
		
		GROUP BY t1.ser_code, t1.libimei_money) t0
		,
		(SELECT t1.ser_code, t1.libimei_money, SUM(t1.libimei_bonus) AS bonus, SUM(t1.libimei_price_purchase) AS price_purchase FROM
		
		(SELECT l.lib_code, l.ser_code, l.lib_date, li.libimei_code, li.libimei_bonus, li.libimei_money, li.libimei_price_purchase FROM 
		(SELECT l.lib_code, l.ser_code, l.lib_date FROM liberations l JOIN client c ON l.cli_code=c.cli_code WHERE c.cli_type <> 3) l 
		JOIN operator_notice li
		ON l.lib_code=li.lib_code WHERE li.libimei_status=1 ".$where.") t1 
		
		GROUP BY t1.ser_code, t1.libimei_money) t1
		WHERE t0.ser_code=t1.ser_code AND t0.libimei_money=t1.libimei_money
		) t1
		 JOIN services s
		ON t1.ser_code=s.ser_code ".$order_by.";";
		
		//echo ("<pre>".$sql."</pre>");
		
		if ( $result = mysql_query ($sql) ){
			$i = 0;
			while ( $row = mysql_fetch_array ($result) ){
				$data[$i]->ser_name = $row["ser_name"];
				$data[$i]->ser_code = $row["ser_code"];
				$data[$i]->libimei_money = $row["libimei_money"];
				$data[$i]->cant = $row["cant"];
				$data[$i]->bonus = $row["bonus"];
				$data[$i]->price_purchase = $row["price_purchase"];
				$data[$i]->ser_price_purchase = $row["ser_price_purchase"];
				++$i;
			}
			
			return $data;
		}
		
		return NULL;
	}
	
	/**
	*	Funcion responsable de obtener el listado de liberaciones que no tienen precio de compra por rangos de fecha.
	*	@author: JSL
	*	@date: 10/ABR/2012
	*/
	function syncUnlockPricePurchasePending($dateFrom = '', $dateTo = ''){
		$where = '';
		if ( $dateFrom != '' )
			$where = " AND libimei_date > '".$dateFrom."' ";
		if ( $dateTo != '' )
			$where .= " AND libimei_date < '".$dateTo."' ";	
			
		$sql = "
		SELECT t.libimei_code, t1.ser_price_purchase FROM
		(SELECT li.libimei_code, li.lib_code FROM operator_notice li WHERE (li.libimei_price_purchase IS NULL OR li.libimei_price_purchase=0) ".$where.") t
		JOIN
		( SELECT l.lib_code, s.ser_price_purchase FROM liberations l JOIN services s ON l.ser_code=s.ser_code WHERE s.ser_price_purchase > 0 ) t1
		ON t.lib_code=t1.lib_code
		";
		
		$result = "NO results";
		$ok = 0;
		$err = 0;
		
		if ( $result = mysql_query ($sql) ) {
			while ( $row = mysql_fetch_array ($result) ) {
				$sqlAux = "UPDATE operator_notice SET libimei_price_purchase='".$row["ser_price_purchase"]."' WHERE libimei_code=".$row["libimei_code"]."";
				if ( mysql_query ( $sqlAux ) )
					++$ok;
				else
					++$err;
			}
			
			if ( $ok > 0 || $err > 0 )
				$result = "Liberaciones procesadas: ".$ok.", Errores: ".$err;
		}
		return $result;
	}
	
	/**
	 * Esta funcion es para guardar lso datos de respaldo de cada liberacion	    
	 * @access public
	 */		
	
	public function addRegisterBackrest($data){
       
       	 	$sql="INSERT INTO `backrest` (`back_code`,`back_date`,`back_name`, `back_last_name`, `back_code_country`, `back_payment`, `back_code_client`, ";
			$sql.=" `back_status_client`,`back_email_client_session`,`back_bonus_client`,`back_email_client_form`,`back_ser_code_form`,`back_imei`,`back_ip`";
			$sql.=" ) VALUES (";
			$sql.="NULL,'$data->backDate','$data->nameCliente','$data->lastnameClient','$data->code_country','$data->paymForm','$data->codeClient',";
			$sql.="'$data->statusClient','$data->emailClient','$data->bonusClient','$data->cliente','$data->servicio','$data->imei','$data->ip')";
			echo $sql;
			if(mysql_query($sql))
			   	return true;
			else
				return false;	
 	}
	
	/**
	*	Funcion encargada de retornar un arreglo con los datos estaditicos de un cliente
	*	@autor: DMHG
	*	@date: 02/11/2012
	*/
	function getServicesConsumeClient($cli_code){
		
		//$sql = "SELECT ser_code FROM liberations WHERE cli_code = ".$cli_code." GROUP BY ser_code;";
		$sql = "SELECT li.lib_code, se.ser_code, ser_name, op.libimei_code, libimei_status from liberations AS li, services AS se, operator_notice AS op where li.ser_code = se.ser_code AND li.lib_code = op.lib_code AND cli_code =".$cli_code;
		
		if ( $result = mysql_query ($sql) ){
			$i = 0;
			$cantPend = 0;
			$cantLib = 0;
			$cantNot = 0;
			$data = array();

			while ( $row = mysql_fetch_array ($result) ){
				
				
				if($row["libimei_status"] == 0)
					$aux1 = $data[$row['ser_code']]->cantPend+1;
				
				if($row["libimei_status"] == 1)
					$aux2 = $data[$row['ser_code']]->cantLib+1;
					
				if($row["libimei_status"] == 3)
					$aux3 = $data[$row['ser_code']]->cantNot+1;
					
				$data[$row['ser_code']]->cantTotal = $aux1 + $aux2 + $aux3;
				
				
				$data[$row['ser_code']]->cantPend	= $aux1;
				$data[$row['ser_code']]->cantLib 	= $aux2;
				$data[$row['ser_code']]->cantNot 	= $aux3;
				$data[$row['ser_code']]->ser_code 	= $row['ser_code'];
				
				if(!isset($data[$row['ser_code']]->ser_name))
				{
					$data[$row['ser_code']]->ser_name = $row['ser_name'];
				}
				
				/*$data[$i]->ser_code = $row["ser_code"];
				
				$sql2="SELECT count(l.ser_code) AS cant, ser_name FROM liberations l, services s WHERE cli_code = ".$cli_code." AND l.ser_code=".$data[$i]->ser_code." AND s.ser_code = l.ser_code";
				$row2 = mysql_fetch_array(mysql_query($sql2));
				
				//Contar ordenes pedientes
				$sql3="SELECT count(op.lib_code) AS cant3 FROM liberations l, operator_notice op WHERE l.cli_code = ".$cli_code." AND l.ser_code=".$data[$i]->ser_code." AND l.lib_code=op.lib_code AND op.libimei_status = 0";
				$row3 = mysql_fetch_array(mysql_query($sql3));
				
				//Contar ordenes liberadas
				$sql4="SELECT count(op.lib_code) AS cant4 FROM liberations l, operator_notice op WHERE l.cli_code = ".$cli_code."  AND l.ser_code=".$data[$i]->ser_code." AND l.lib_code=op.lib_code AND op.libimei_status = 1";
				$row4 = mysql_fetch_array(mysql_query($sql4));
				
				//Contar ordenes not in database
				$sql5="SELECT count(op.lib_code) AS cant5 FROM liberations l, operator_notice op WHERE l.cli_code = ".$cli_code."  AND l.ser_code=".$data[$i]->ser_code." AND l.lib_code=op.lib_code AND op.libimei_status = 3";
				$row5 = mysql_fetch_array(mysql_query($sql5));
				
				$sql6="SELECT count(l.ser_code) AS cant6 FROM liberations l WHERE cli_code = ".$cli_code;
				$row6 = mysql_fetch_array(mysql_query($sql6));*/
				
				/*$data[$i]->ser_cant = $row2["cant"];
				$data[$i]->ser_name = $row2["ser_name"];
				$data[$i]->ser_cantPending = $row3["cant3"];
				$data[$i]->ser_cantResolved = $row4["cant4"];
				$data[$i]->ser_cantNoLib = $row5["cant5"];
				$data[$i]->ser_cant_total = $cant;*/
				
				$i++;
			}
			
			
			return $data;
		}
		
		return NULL;
	}
	
	//////////////////////////////////////////////////////////////////////////////////
	// editClient      : Esta funcion es la encargada de editar los datos de un client
	// parameter     : $new_data -> es el array con los datos a editar
	//////////////////////////////////////////////////////////////////////////////////
	function updateAllPriceServices($ser_code,$price_col,$price_us,$price_bol)
	{
		$sql=
		"UPDATE `services` SET 
		`ser_price_col_reseller`     = '".$price_col."',
		`ser_price_us_reseller`     = '".$price_us."',
		`ser_price_bol_reseller`   = '".$price_bol."'
		 WHERE ser_code = ".$ser_code.";";
	//die($sql);
	if(mysql_query($sql))
	   		return true;
	else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	///////////////////////////////////////////////////////////////////////////////
	// updateOrder: Actualiza cada una de las posiciones de los servicios.
	//
	// @Param:	$Codigo   Codigo del servicio
	// @Param:  $Order    Order del Servicio
	//
	// Autor: Steven Salcedo O
	// Fecha de Creación: 13/11/2012
	//
	// Modificaciones:
	///////////////////////////////////////////////////////////////////////////////
	
			
	public function updatePosition($ser_code,$orderItem)
	{
		$sql = "UPDATE services SET ser_order='".$orderItem."' WHERE ser_code = ".$ser_code;
		
		if(mysql_query($sql))
		   return true;
		else
		{
			securityMasterUnlock(53,1,$sql);
			return false;
		}    
	}
	
	public function saveCodesLiberation($code,$ser_code){
		$sql="INSERT INTO `service_codes` (`serco_code`,`ser_code`, `serco_code_liberation`, `serco_status`) VALUES (NULL,'".$ser_code."','".$code."',0)";

		if(mysql_query($sql))
			return mysql_insert_id();
		else{
			securityMasterUnlock(64,1,$sql);
			return false;
		}	
 	}
	
	function checkExistCodesLiberation($code,$ser_code)
	{
		$sql = "SELECT count(serco_code) as cant FROM service_codes WHERE ser_code=".$ser_code." AND serco_code_liberation=".$code;
		//die();
		if($consult = mysql_query($sql)){
	
			$data = array();
			$row=mysql_fetch_array($consult);
			
			return $row['cant'];
		}
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}
	}
	
	function listCodesService($ser_code){
		
		$sql = "SELECT serco_code_liberation FROM service_codes WHERE serco_status=0 AND ser_code=".$ser_code." ORDER BY serco_code ASC";
		
		if($consult = mysql_query($sql)) {
			if($row = mysql_fetch_array($consult)){
				return $row['serco_code_liberation'];
			}
			else return false;
		}
		else{
			return false;
		}
	}
	
	public function changeStatusCodesLiberation($serco_code_liberation)
	{
		$sql = "UPDATE service_codes SET serco_status='1' WHERE serco_code_liberation = ".$serco_code_liberation;
		
		if(mysql_query($sql))
		   return true;
		else
		{
			securityMasterUnlock(53,1,$sql);
			return false;
		}    
	}
	
	function listCodesServicePending($ser_code){
		
		$sql = "SELECT serco_code,serco_code_liberation,serco_status FROM service_codes WHERE ser_code=".$ser_code." ORDER BY serco_code ASC";
		
		if ( $result = mysql_query ($sql) ){
			$i = 0;
			while ( $row = mysql_fetch_array ($result) ){
				
				$data[$i]->serco_code = $row["serco_code"];
				$data[$i]->serco_code_liberation = $row["serco_code_liberation"];
				$data[$i]->serco_status = $row["serco_status"];
				
				$i++;
			}
			
			return $data;
		}
		
		return NULL;
	}
	
	public function deleteListCode($code)
	{
		$sql = "DELETE FROM `service_codes` WHERE `serco_code` = ".$code;
	 	if(mysql_query($sql))
		  return true;
		else{
				securityMasterUnlock(64,1,$sql);
				return false;
			}  
	}
	
}
?>